/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package logical_spaces.business;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import logical_spaces.DAO.TableSpaceDAO;

/**
 *
 * @author juan
 */
public class TableSpaceBusiness {
    
    public Connection conn;
    
    public TableSpaceBusiness( Connection connection) {
        
        conn = connection;
        
    }
    
   public String createTableSpaceTemporary(String nameIn, String sizeIn, String tableSizeOption, String fileNameIn, String fileSizeValue, String uniformSizeIn, String uniformSizeValueIn, String uniformSizeOption, String autoExtendIn, String autoExtendIncrementoIn, String autoExtendMazSizeIn, String autoExtendSizeOption, String autoExtendMaxSizeOption){
       
       return crearQueryTemporary(nameIn, sizeIn, tableSizeOption, fileNameIn, fileSizeValue, uniformSizeIn, uniformSizeValueIn, uniformSizeOption, autoExtendIn, autoExtendIncrementoIn, autoExtendMazSizeIn, autoExtendSizeOption, autoExtendMaxSizeOption);
          
    }
   
   public String createTableSpaceUndo(String nameIn, String sizeIn, String tableSizeOption, String fileNameIn, String fileSizeValue, String tableStateIn, String uniformSizeIn, String autoExtendIn, String autoExtendIncrementoIn, String autoExtendMazSizeIn, String autoExtendSizeOption, String autoExtendMaxSizeOption){
           
       return crearQueryUndo(nameIn, sizeIn, tableSizeOption, fileNameIn, fileSizeValue, tableStateIn, uniformSizeIn, autoExtendIn, autoExtendIncrementoIn, autoExtendMazSizeIn, autoExtendSizeOption, autoExtendMaxSizeOption);
          
    }
   
   public String createTableSpacePermanent(String nameIn, String sizeIn, String tableSizeOption, String fileNameIn, String fileSizeValue, String tableStateIn, String uniformSizeIn, String uniformSizeValueIn, String uniformSizeOption,String autoExtendIn, String autoExtendIncrementoIn, String autoExtendMazSizeIn, String autoExtendSizeOption, String autoExtendMaxSizeOption){
           
      return crearQueryPermanent(nameIn, sizeIn, tableSizeOption ,fileNameIn, fileSizeValue, tableStateIn, uniformSizeIn, uniformSizeValueIn, uniformSizeOption,autoExtendIn, autoExtendIncrementoIn, autoExtendMazSizeIn, autoExtendSizeOption, autoExtendMaxSizeOption);
         
   }
   
   public String crearQueryAgregarDataFile(String tableSpace,String name, String tamano, String unidad){
       
       String query = "ALTER TABLESPACE \"" + tableSpace + "\" ADD DATAFILE " +"'/app/oracle/product/12.1.0/dbhome_1/"+ name+ ".dbf"+"' SIZE " + tamano + unidad;
        try {
            TableSpaceDAO tableSpaceDAO = new TableSpaceDAO(conn);
            return tableSpaceDAO.modificarTablespace(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
            return ex.getMessage();
        }
       
   } 
   
   public String crearQueryAgregarTempFile(String tableSpace,String name, String tamano, String unidad){
       
       String query = "ALTER TABLESPACE \"" + tableSpace + "\" ADD TEMPFILE " +"'/app/oracle/product/12.1.0/dbhome_1/"+ name+ ".dbf"+"' SIZE " + tamano + unidad; 
        try {
            TableSpaceDAO tableSpaceDAO = new TableSpaceDAO(conn);
            return tableSpaceDAO.modificarTablespace(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
            return ex.getMessage();
        }
       
   } 
   
   public String dropTablespacesQuery(String tableSpace){
       
       String query = "DROP TABLESPACE \"" + tableSpace + "\" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS"; 
       
       System.out.println(query);
        try {
            TableSpaceDAO tableSpaceDAO = new TableSpaceDAO(conn);
            return tableSpaceDAO.eliminarTablespace(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
            return ex.getMessage();
        }
        
   }
   
   public String crearQueryEstado(String tableSpace,String estado){

       String query = "ALTER TABLESPACE \"" + tableSpace + "\" " + estado; 
       System.out.println(query);
        try {
            TableSpaceDAO tableSpaceDAO = new TableSpaceDAO(conn);
            return tableSpaceDAO.modificarTablespace(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
            return ex.getMessage();
        }
   
   }
   
    public String crearQueryPermanent(String nameIn, String sizeIn, String tableSizeOption, String fileNameIn, String fileSizeIn, String tableSpaceStateIn, String uniformSizeIn, String uniformSizeValueIn, String uniformSizeOption,String autoExtendIn, String autoExtendValueIn, String autoExtendMaxSizeIn, String autoExtendSizeOption, String autoExtendMaxSizeOption) {
        
        String fileSize = "";
        String tableSpaceState = "";
        String uniformSize = "";
        String autoExtend = "";

        //TRADUCIENDO FILE SIZE
        if (fileSizeIn.equals("radio1opt1")) {
            fileSize = "SMALLFILE";
        }
        if (fileSizeIn.equals("radio1opt2")) {
            fileSize = "BIGFILE";
        }

        //TRADUCIENDO TABLE STATE
        if (tableSpaceStateIn.equals("radio2opt1")) {
            tableSpaceState = "ONLINE";
        }
        if (tableSpaceStateIn.equals("radio2opt2")) {
            tableSpaceState = "OFFLINE";
        }
        
        System.out.println("------>" + uniformSizeIn);
        //TRADUCIENDO UNIFORM SIZE
        if (uniformSizeIn.equals("radio3opt1")) {
            uniformSize = "UNIFORM";
            if (!uniformSizeValueIn.equals("")) {
                uniformSize = "UNIFORM SIZE " + uniformSizeValueIn + uniformSizeOption;
            }
        }
        if (uniformSizeIn.equals("radio3opt2")) {
            uniformSize = "AUTOALLOCATE";
        }

        //TRADUCIENDO AUTOEXTEND
        if (autoExtendIn.equals("radio4opt1")) {
            autoExtend = "AUTOEXTEND ON";
            if (!autoExtendValueIn.equals("") || !autoExtendMaxSizeIn.equals("")) {
                if (!autoExtendValueIn.equals("")) {
                    autoExtend = autoExtend + " NEXT " + autoExtendValueIn + autoExtendSizeOption;
                }
                if (!autoExtendMaxSizeIn.equals("")) {
                    autoExtend = autoExtend + " MAXSIZE " + autoExtendMaxSizeIn + autoExtendMaxSizeOption;
                }
            }
        }
        if (autoExtendIn.equals("radio4opt2")) {
            autoExtend = "AUTOEXTEND OFF";
        }

        //CREANDO QUERY
        String query = "CREATE " + fileSize + " TABLESPACE \"" + nameIn + "\" DATAFILE \'" + fileNameIn + "\' SIZE " + sizeIn + tableSizeOption + " " + autoExtend + " " + tableSpaceState + " EXTENT MANAGEMENT LOCAL " + uniformSize;
        System.out.println(query);
        try {
            TableSpaceDAO tableSpaceDAO = new TableSpaceDAO(conn);
            return tableSpaceDAO.createTableSpace(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return query;
    }
    
    public String crearQueryUndo(String nameIn, String sizeIn, String tableSizeOption, String fileNameIn, String fileSizeIn, String tableSpaceStateIn, String uniformSizeIn, String autoExtendIn, String autoExtendIncrementoValueIn, String autoExtendMaxSizeIn, String autoExtendSizeOption, String autoExtendMaxSizeOption){
        
        String fileSize="";
        String tableSpaceState="";
        String uniformSize="";
        String autoExtend="";
        
        //TRADUCIENDO FILE SIZE
        if(fileSizeIn.equals("radio1opt1"))
            fileSize="SMALLFILE";
        if(fileSizeIn.equals("radio1opt2"))
            fileSize="BIGFILE";         
        
        //TRADUCIENDO TABLE STATE
        if(tableSpaceStateIn.equals("radio2opt1"))
            tableSpaceState="ONLINE";
        if(tableSpaceStateIn.equals("radio2opt2"))
            tableSpaceState="OFFLINE";
        
        System.out.println("------>" + uniformSizeIn);
        //TRADUCIENDO UNIFORM SIZE
        if(uniformSizeIn.equals("radio3opt2"))
            uniformSize="AUTOALLOCATE";
        
        //TRADUCIENDO AUTOEXTEND
        if(autoExtendIn.equals("radio4opt1")){
            autoExtend="AUTOEXTEND ON";
            if(!autoExtendIncrementoValueIn.equals("") || !autoExtendMaxSizeIn.equals("")){
                if(!autoExtendIncrementoValueIn.equals(""))
                    autoExtend = autoExtend + " NEXT " + autoExtendIncrementoValueIn + autoExtendSizeOption;
                if(!autoExtendMaxSizeIn.equals(""))
                    autoExtend = autoExtend + " MAXSIZE " + autoExtendMaxSizeIn + autoExtendMaxSizeIn;
            }
        }
        if(autoExtendIn.equals("radio4opt2"))
            autoExtend="AUTOEXTEND OFF";
        
        //CREANDO QUERY
        String query = "CREATE " + fileSize + " UNDO TABLESPACE \""  + nameIn + "\" DATAFILE \'" + fileNameIn + "\' SIZE " + sizeIn + tableSizeOption + " " + autoExtend + " " + tableSpaceState + " EXTENT MANAGEMENT LOCAL " + uniformSize;
        System.out.println(query);
        try {
            TableSpaceDAO tableSpaceDAO = new TableSpaceDAO(conn);
            return tableSpaceDAO.createTableSpace(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        return query;
    }
    
    
    public String crearQueryTemporary(String nameIn, String sizeIn, String tableSizeOption,String fileNameIn, String fileSizeIn, String uniformSizeIn, String uniformSizeValueIn, String uniformSizeOption, String autoExtendIn, String autoExtendIncrementoValueIn, String autoExtendMaxSizeIn, String autoExtendSizeOption, String autoExtendMaxSizeOption){
        
        String fileSize="";
        String uniformSize="";
        String autoExtend="";
        
        //TRADUCIENDO FILE SIZE
        if(fileSizeIn.equals("radio1opt1"))
            fileSize="SMALLFILE";
        if(fileSizeIn.equals("radio1opt2"))
            fileSize="BIGFILE";         
        
        System.out.println("------>" + uniformSizeIn);
        //TRADUCIENDO UNIFORM SIZE
        if(uniformSizeIn.equals("radio2opt1")){
            uniformSize="UNIFORM";
            if(!uniformSizeValueIn.equals(""))
                uniformSize="UNIFORM SIZE " + uniformSizeValueIn;
        }
        if(uniformSizeIn.equals("radio2opt2"))
            uniformSize="AUTOALLOCATE";
        
        //TRADUCIENDO AUTOEXTEND
        if(autoExtendIn.equals("radio3opt1")){
            autoExtend="AUTOEXTEND ON";
            if(!autoExtendIncrementoValueIn.equals("") || !autoExtendMaxSizeIn.equals("")){
                if(!autoExtendIncrementoValueIn.equals(""))
                    autoExtend = autoExtend + " NEXT " + autoExtendIncrementoValueIn + autoExtendSizeOption;
                if(!autoExtendMaxSizeIn.equals(""))
                    autoExtend = autoExtend + " MAXSIZE " + autoExtendMaxSizeIn + autoExtendMaxSizeOption;
            }
        }
        if(autoExtendIn.equals("radio3opt2"))
            autoExtend="AUTOEXTEND OFF";
        
        //CREANDO QUERY
        String query = "CREATE " + fileSize + " TEMPORARY TABLESPACE \""  + nameIn + "\" TEMPFILE \'" + fileNameIn + "\' SIZE " + sizeIn + tableSizeOption + " " + autoExtend + " EXTENT MANAGEMENT LOCAL " + uniformSize + uniformSizeOption;
        System.out.println(query);
        try {
            
            TableSpaceDAO tableSpaceDAO = new TableSpaceDAO(conn);
            return tableSpaceDAO.createTableSpace(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return query;
    }
    
   public String crearQueryEliminarDataFilePerTableSpace(String tableSpace,String dataFile){
       
       String query = "ALTER TABLESPACE \"" + tableSpace + "\" DROP DATAFILE '" + dataFile + "'" ;
        try {
            TableSpaceDAO tableSpaceDAO = new TableSpaceDAO(conn);
            return tableSpaceDAO.modificarTablespace(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
            return ex.getMessage();
        }
       
   }
   
   public String crearQueryEliminarTempFilePerTableSpace(String tableSpace,String tempFile){
       
       String query = "ALTER TABLESPACE \"" + tableSpace + "\" DROP TEMPFILE '" + tempFile + "'" ;
        try {
            TableSpaceDAO tableSpaceDAO = new TableSpaceDAO(conn);
            return tableSpaceDAO.modificarTablespace(query);
        } catch (SQLException ex) {
            Logger.getLogger(TableSpaceBusiness.class.getName()).log(Level.SEVERE, null, ex);
            return ex.getMessage();
        }
       
   }
}
